package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import beans.Category;

public class CategoryDao {

    public List<Object> categoryDetail(String sql, Object[] params){
        final List<Object> results = new ArrayList<>();
        return Query.executeQueryList(results, sql,params, new QueryCallBackList() {
            @Override
            public List<Object> doExecuteList(ResultSet rs, List<Object> results) {
                try {
                    while (rs.next()) {
                        Category category = new Category();
                        category.setCategoryId(rs.getInt("categoryId"));
                        category.setCategoryName(rs.getString("categoryName"));
                        category.setIsFunction(rs.getInt("is_function"));
                        results.add(category);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return results;
            }
        });
    }

	public List<Object> allCategoryList(){
		String sql = "select * from category ";
        return this.categoryDetail(sql,null);
	}

    public List<Object> categoryDetail(String categoryId){
        String sql = "select * from category where categoryId=?";
        return this.categoryDetail(sql,new Object[]{categoryId});
    }

    public List<Object> categoryWithAlgorithm(){
        String sql = "SELECT * from category where categoryId in (SELECT * from ((select function from " +
                "algorithm where is_delete=0) union (select type from algorithm where is_delete=0)) " +
                "as a) ORDER BY categoryId desc";
        return this.categoryDetail(sql,null);
    }

    public boolean addCategory(String name, String isFunction) {
        String sql = "INSERT INTO `category` (categoryName,is_function) VALUES (?,?)";
        return Query.executeDML(sql, new Object[]{name,isFunction})>0;
    }

    public int deleteCategory(String categoryId) {
        if(numOfAlgorithmInCategory(categoryId)>0){
            return -2;
        }else{
            String sql = "delete from category where categoryId = ?";
            return Query.executeDML(sql, new Object[]{categoryId});
        }
    }

    public int numOfAlgorithmInCategory(String categoryId){
        String categorySql = "(select * from algorithm where `algorithm`.`function`=?) union all (select * from algorithm where `algorithm`.type = ?)";
        return (int) Query.executeQueryObject(categorySql, new Object[]{categoryId, categoryId}, new QueryCallBack() {
            @Override
            public Object doExecute(ResultSet rs) {
                int result = 0;
                try {
                    while(rs.next()){
                        result++;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return result;
            }
        });
    }

    public boolean alterCategory(String categoryId, String categoryName, String isFunction) {
        String sql = "update category set categoryName = ? ,is_function = ? where categoryId = ?";
        return Query.executeDML(sql, new Object[]{categoryName,isFunction,categoryId})>0;
    }

    public List<Object> showAllCategory(Integer onePageCount, Integer currentPage) {
        String sql = "SELECT * from category order by categoryId desc limit "
                + (currentPage - 1) * onePageCount + " , " + onePageCount;
        return this.categoryDetail(sql,null);
    }

    public int categoryPageNum(Integer onePageCount) {
        int companyNum = this.categoryNum();
        return (companyNum % onePageCount)==0? (companyNum / onePageCount) : (companyNum / onePageCount + 1);

    }

    public synchronized int categoryNum() {
        String sql = "select count(*) from category";
        return PublicDao.oneNum(sql,null);
    }

    public List<Object> showOneCategory(String categoryId) {
        String sql = "SELECT categoryId,categoryName,is_function from category where categoryId = ? ";
        Object[] params = new Object[]{categoryId};
        return this.categoryDetail(sql, params);
    }

    public static void main(String[] args) {
    }
}

